CHARACTERCASE Code Sample 1

-- Code Generator for expression

DECLARE @x  INT, @c  CHAR(1), @sql VARCHAR(8000)

SET @x = 0 
SET @sql = '@str'  -- actual variable/column you want to replace

WHILE @x < 26

BEGIN    SET @c = CHAR(ASCII('a') + @x)    

SET @sql = 'REPLACE(' + @sql + ', '' ' + @c+  ''', '' ' + UPPER(@c) + ''')'    
SET @x = @x + 1 

END

PRINT @sql



-----------------------------------------------------------------------

SAMPLE 2



Create Table Import_Data_Filter (MyID Int Identity(1,1) NOT NULL, Location varchar(100))
GO

Insert into Import_Data_Filter (Location)
(Select Upper('Mandarin') UNION ALL Select Upper('San Jose') UNION ALL Select Upper('Baymeadows') UNION ALL
Select Upper('My FH Locale') UNION ALL Select Upper('St. Augustine') UNION ALL Select Upper('Test For Three Spaces')
UNION ALL Select Upper('Test for being Four Spaces') UNION ALL Select Upper('Test for being Five More Spaces') 
UNION ALL Select Upper('Baymeadows') UNION ALL Select Upper('St. Augustine'))
GO



----------------------------------------------------------------------------

SAMPLE 3

If (Select Object_ID('tempdb..#MyTemp1')) is NOT NULL
  Drop table #MyTemp1; -- Drop temp table if it already exists

Select Distinct Location into dbo.#MyTemp1 from dbo.Import_Data_Filter; --Get distinct values for all locations

Update mt1 Set Location = Lower(mt1.Location) from dbo.#MyTemp1 mt1;  -- Set entire string to lower case letters

Update mt1 Set Location = UPPER(Substring(mt1.Location,1,1)) 	+ Substring(mt1.Location,2,Len(mt1.Location))
from dbo.#MyTemp1 mt1; -- Set very first character in string to Upper case and add in lower case rest of string

--Select * from dbo.#MyTemp1; -- For troubleshooting only

Alter Table dbo.#MyTemp1
  Add Done bit NOT NULL Default 0;

GO

------------------------------------------------------------------------------